import random
import pymysql
import datetime
from decimal import Decimal
from iotdb.dbapi import connect
import templates

com_json=templates.DATA_JSON()


class OPEN_MYSQL:
    def __init__(self):
        self.mysql_host = "127.0.0.1"
        self.mysql_port = 3306
        self.mysql_username = "root"
        self.mysql_password = "123456"
        self.database="opendigger"

    def Connect(self):
        self.con = pymysql.connect(
            host=self.mysql_host,
            port = self.mysql_port,
            user = self.mysql_username,
            password = self.mysql_password,
            database = self.database,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor      # 查询结果返回为字典格式，其中字段名作为字典的键
        )

    def open_mysql(self):
        self.Connect()
        self.cursor = self.con.cursor()

    def close_mysql(self):
        self.cursor.close()
        self.con.close()

    def data_predict(self, item_name):
        self.Connect()
        self.cursor = self.con.cursor()
        try:
            sql = f"select future_data from lstm_predicts WHERE item='{item_name}'"
            # 执行sql命令
            self.cursor.execute(sql)
            # 获取所有查询结果
            results = self.cursor.fetchall()
            return results

        except Exception as e:
            print(e)
        finally:
            self.cursor.close()
            self.con.close()

        # 执行sql
    def top100_sql(self):
        try:
            sql='''
            select 
            i.*,
            c.CSI
            FROM 
            csi_index AS c
            LEFT JOIN items_gather AS i ON c.item=i.item
            ORDER BY c.CSI DESC
            '''
            # 执行sql命令
            self.cursor.execute(sql)
            # 获取所有查询结果
            results = self.cursor.fetchall()
            results.insert(0, 'top100总数据')
            return results
        except Exception as e:
            print(e)

    def top10_sql(self):
        try:
            sql="select UNIX_TIMESTAMP(times) AS times,people_top10 from item_people ORDER BY `times` DESC LIMIT 10"
            # 执行sql命令
            self.cursor.execute(sql)
            # 获取所有查询结果
            results = self.cursor.fetchall()
            results.insert(0, 'top10项目人数')
            return results

        except Exception as e:
            print(e)

    def act_proportion(self):
        dirs_list=[]
        for i in range(10):
            table, goal = com_json.dir_json(i)
            dirs_list.append(table)
        # 从项目中随机选取一个作为开发者活跃度占比
        dir = random.sample(dirs_list, 1)

        try:
            sql=f"select * from act_proportion WHERE item='{dir[0]}'"
            # 执行sql命令
            self.cursor.execute(sql)
            # 获取所有查询结果
            results = self.cursor.fetchall()
            return results

        except Exception as e:
            print(e)


class OPEN_IOTDB:
    def __init__(self):
        self.iotdb_ip = "127.0.0.1"
        self.iotdb_port = "6667"
        self.iotdb_username = "root"
        self.iotdb_password = "root"

    def iotdb(self):
        self.conn = connect(
            self.iotdb_ip,
            self.iotdb_port,
            self.iotdb_username,
            self.iotdb_password,
            fetch_size=1024,
            zone_id="UTC+8",
            sqlalchemy_mode=False
        )

    def activity_tend(self):
        dirs_list = []
        for i in range(10):
            table, goal = com_json.dir_json(i)
            dirs_list.append(table)
        # 前端活动趋势展示的数据个数
        dirs_list = random.sample(dirs_list, 5)
        self.iotdb()
        self.cursor = self.conn.cursor()

        data = []
        for dir in dirs_list:
            sql = f"select activity from root.opendigger.{dir} ORDER BY TIME DESC LIMIT 10"
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            for item in result:
                timestamp = item[0] / 1000 + 86400  # 时间戳除以1000，转换为秒
                date = datetime.datetime.utcfromtimestamp(timestamp).strftime('%Y-%m-%d')  # 转换为年月日
                item[0] = date  # 更新原数据中的时间戳部分
            result.insert(0, dir)
            # 获取预测数据
            ope = OPEN_MYSQL()
            predict = ope.data_predict(dir)
            result.append(predict)
            data.append(result)

        self.cursor.close()
        self.conn.close()
        return data


def decimal_default(obj):
    if isinstance(obj, Decimal):
        return float(obj)  # 将 Decimal 转换为 float

def inquire():
    select=OPEN_MYSQL()
    model = OPEN_IOTDB()

    select.open_mysql()         # 打开数据库
    top100_results=select.top100_sql()
    top10_results = select.top10_sql()
    acts_proportion=select.act_proportion()
    select.close_mysql()        # 关闭数据库

    activity_tende=model.activity_tend()
    return top100_results,top10_results,acts_proportion,activity_tende

